/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package mpinventory;

import java.sql.*;
import java.util.List;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
/**
 *
 * @author user
 */
public class CustomerQuery {
    private Connection conn;
    public Customer customer;
    private String searchBy;
    public CustomerQuery(Connection conn){
        this.conn=conn;
    }
    public boolean canAddCustomer(String customerName, String customerAddress){
        String query="select customerName FROM customerdata where customerName = ? AND customerAddress = ?";
        try{
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setString(1, customerName);
            prest.setString(2, customerAddress);
            ResultSet rs=prest.executeQuery();
            while(rs.next())
                return false;
        }
        catch(SQLException ex){
            Logger.getLogger(CustomerQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return true;
    }
    public void addCustomer(int customerID, String customerName, String customerAddress, String customerContactNumber, String customerRemarks){
        String query="insert into customerdata(customerID, customerName, customerAddress, customerContactNumber, customerRemarks) values(?,?,?,?,?)";
        try{
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setInt(1, customerID);
            prest.setString(2, customerName);
            prest.setString(3, customerAddress);
            prest.setString(4, customerContactNumber);
            prest.setString(5, customerRemarks);
            prest.executeUpdate();
            JOptionPane.showMessageDialog(null, "Successfully added " + customerName + " to the database!");
        }
        catch(SQLException ex){
            Logger.getLogger(CustomerQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    public List<Customer> initializeCustomers(){
        List<Customer> customers=new ArrayList<Customer>();
        try{
            Statement stmt=conn.createStatement();
            String query="select * from customerdata ORDER BY customerName ASC";
            ResultSet rs=stmt.executeQuery(query);
            while(rs.next()){
                Customer customer=new Customer();
                customer.setID(rs.getInt("customerID"));
                customer.setName(rs.getString("customerName"));
                customer.setAddress(rs.getString("customerAddress"));
                customer.setContactNumbers(rs.getString("customerContactNumber"));
                customer.setRemarks(rs.getString("customerRemarks"));
                customers.add(customer);
            }
        }
        catch(SQLException ex){
            Logger.getLogger(CustomerQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return customers;
    }
    public void editCustomer(int customerID, String customerName, String customerAddress, String customerContactNumber, String customerRemarks){
        String query="update customerdata set customerName=?, customerAddress=?, customerContactNumber=?, customerRemarks=? where customerID=?";
        try{
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setString(1, customerName);
            prest.setString(2, customerAddress);
            prest.setString(3, customerContactNumber);
            prest.setString(4, customerRemarks);
            prest.setInt(5, customerID);
            prest.executeUpdate();
            JOptionPane.showMessageDialog(null, "Successfully edited " + customerName + "!");
        }
        catch(SQLException ex){
            Logger.getLogger(CustomerQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    public void interpretSearchBy(int n){
        if(n==0)
            searchBy="All";
        else if(n==1)
            searchBy="Name";
        else if(n==2)
            searchBy="Address";
        else if(n==3)
            searchBy="Contact Number/s";
        else if(n==4)
            searchBy="Remarks";
    }
    public List<Customer> search(String text){
        List<Customer> customers=new ArrayList<Customer>();
        String query;
        if(searchBy.equals("Name"))
            query="select * FROM customerdata where customerName like ? ORDER BY customerName ASC";
        else if(searchBy.equals("Address"))
            query="select * FROM customerdata where customerAddress like ? ORDER BY customerName ASC";
        else if(searchBy.equals("Contact Number/s"))
            query="select * FROM customerdata where customerContactNumber like ? ORDER BY customerName ASC";
        else
            query="select * FROM customerdata where customerRemarks like ? ORDER BY customerName ASC";
        try{
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setString(1, "%"+text+"%");
            ResultSet rs=prest.executeQuery();
            while(rs.next()){
                Customer customer=new Customer();
                customer.setID(rs.getInt("customerID"));
                customer.setName(rs.getString("customerName"));
                customer.setAddress(rs.getString("customerAddress"));
                customer.setContactNumbers(rs.getString("customerContactNumber"));
                customer.setRemarks(rs.getString("customerRemarks"));
                customers.add(customer);
            }
        }
        catch(SQLException ex){
            Logger.getLogger(CustomerQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        if(customers.isEmpty()){
            JOptionPane.showMessageDialog(null, "The keyword that you have just typed in does not belong to any customer or falls under the wrong category. Please check your input.");
            return null;
        }
        return customers;
    }
    public int setID(){
        try{
            Statement stmt=conn.createStatement();
            String query="select max(customerID) as maxID from customerdata";
            ResultSet rs=stmt.executeQuery(query);
            while(rs.next())
                return rs.getInt("maxID")+1;
        }
        catch(SQLException ex){
            Logger.getLogger(CustomerQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }
}
